if exists (select 1 from sysobjects where name = 'get_dienstnotinschema' and type = 'P')
begin
   drop procedure get_dienstnotinschema
   print 'Procedure: get_dienstnotinschema deleted ...'
end
go
create procedure get_dienstnotinschema(
  @schemaid     int = 15,
  @abteilungid  int = 1
)
as
begin
  set nocount on

  if @abteilungid = 0 select @abteilungid = 1

  select dienstid = d.DienstID, 
         gerechnet = 0, 
         einsatzprotag = 0, 
         dienstname = d.Name, 
         kurztext = b.Kurztext,
         langtext = b.Langtext,
         abteilungid = 0,
         abteilungname = ''
    from Dienst d
   inner join Beschreibung b
      on d.BeschrID = b.BeschrID
   where d.DienstID not in (select ds.DienstID
                              from DienstSchema ds
                             where ds.SchemaID = @schemaid 
                               and (ds.AbteilungID = @abteilungid))
    and d.Status = 0
                              

end
go
print 'Procedure: get_dienstnotinschema done ...'
go

grant exec on get_dienstnotinschema to prsadmins with grant option
go
grant exec on get_dienstnotinschema to prsusers
go

